
import pandas as pd
pd.set_option('display.max_columns', 115)
pd.set_option('display.max_rows', 100)


df_ad_sys = pd.read_csv('ad_sys.csv')
df_billing_sys = pd.read_csv('billing_sys.csv')
df_internal_events = pd.read_csv('internal_events.csv')
df_uid_to_user_id = pd.read_csv('uid_to_user_id.csv')


df_ad_sys.head()


df_billing_sys.head()


df_internal_events.head()


df_uid_to_user_id[:5]


df_ad_sys.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 775 entries, 0 to 774
Data columns (total 5 columns):
 #   Column         Non-Null Count  Dtype  
---  ------         --------------  -----  
 0   date           775 non-null    object 
 1   revenue        775 non-null    float64
 2   spent          775 non-null    float64
 3   _collected_at  775 non-null    object 
 4   affid          775 non-null    int64  
dtypes: float64(2), int64(1), object(2)
memory usage: 30.4+ KB


df_ad_sys.value_counts('affid') # у нас один источник трафика
# но давайте попробуем сделать сводную таблицу по источникам трафика, допустив, что в будущем их станет больше

affid
1008    775
dtype: int64


print('Тип даты' ,type(df_ad_sys['date'][0])) # дата в формате строки, из лучших побуждений приведем её в datetime
df_ad_sys['date'] = pd.to_datetime(df_ad_sys['date'],  format='%Y-%m-%d %H:%M:%S')
print('Тип даты' ,type(df_ad_sys['date'][0]))
df_ad_sys.value_counts('date')

Тип даты <class 'str'>
Тип даты <class 'pandas._libs.tslibs.timestamps.Timestamp'>

date
2021-04-12    169
2021-04-11    169
2021-04-10    158
2021-04-09    140
2021-04-08    139
dtype: int64


df_ad_sys[138:148]# я предполагаю, что revenue имеет накопительный характер в течении дня
# значит в в groupby будем брать максимальное значение, а не сумму


df_ad_sys_svod = df_ad_sys.groupby('date').max()
df_ad_sys_svod
# на основании этих данных построим график, чтобы визуализировать нашу таблицу для более понятного представления


date_parametr = input('Введите через пробел две даты: yyyy-mm-dd yyyy-mm-dd: ')
date_parametr = date_parametr.split(' ')
date_parametr = pd.to_datetime(date_parametr,  format='%Y-%m-%d %H:%M:%S')
# 2021-04-09 2021-04-11

Введите через пробел две даты: yyyy-mm-dd yyyy-mm-dd: 2021-04-09 2021-04-11


df_ad_sys_svod = df_ad_sys_svod[date_parametr[0]:date_parametr[1]]


revenue_traffic_source = df_ad_sys_svod['revenue'].sum()
import plotly.graph_objects as go
fig = go.Figure(go.Indicator(
    title = {'text': "Выручка по данным рекламной системы с {begin} по {end}".format(begin=str(date_parametr[0])[:10], 
                                                                                     end=str(date_parametr[1])[:10])},
     mode = "number+delta",
    value = revenue_traffic_source,
    delta = {'reference': revenue_traffic_source}))
fig.show()


import plotly.graph_objects as go
from plotly.offline import iplot
import cufflinks

cufflinks.go_offline()
trace0 = go.Bar(
    x=df_ad_sys_svod.index,
    y=df_ad_sys_svod.revenue,
    name='revenue df_ad_sys'
)

data = [trace0]
layout = {'title': 'Revenue из Рекламной системы в разрезе дат', 'xaxis': {'title': 'Date'}, 'yaxis': {'title': 'Revenue'}}
fig = go.Figure(data=data, layout=layout)
iplot(fig, show_link=False)


df_billing_sys.info() # Есть пропущенные значения по источникам трафика

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1125 entries, 0 to 1124
Data columns (total 4 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   created_at  1125 non-null   object 
 1   affid       973 non-null    float64
 2   amount      1125 non-null   float64
 3   uid         1125 non-null   object 
dtypes: float64(2), object(2)
memory usage: 35.3+ KB


df_billing_sys[(df_billing_sys['affid'].isna())]


import numpy as np
df_billing_sys.loc[(df_billing_sys['affid'].isna()), 'affid'] = 9999
# что делать с этими значениями? я пожалуй дам им число источника трафика 9999, что бы учесть в общей прибыли, 
# и посмотреть долю в графиках
# Видим, что здесь больше источников трафика, в отличие от рекламной системы, где он один
df_billing_sys['affid'].value_counts()

1008.0    346
1210.0    195
9999.0    152
1999.0    142
1402.0    134
1211.0     41
1209.0     37
1009.0     32
1401.0     21
1997.0      6
1998.0      6
1000.0      4
1238.0      3
1042.0      3
1510.0      1
1026.0      1
1012.0      1
Name: affid, dtype: int64


df_ad_sys.value_counts('date')

date
2021-04-12    169
2021-04-11    169
2021-04-10    158
2021-04-09    140
2021-04-08    139
dtype: int64


df_billing_sys['date'] = df_billing_sys['created_at'].apply(lambda x: x[:10])
df_billing_sys['date'].value_counts()
# здесь нет 12ого числа, в отличие от таблицы только с 1008 id тсточника трафика,
# видимо данные в эту таблицу подгружаются позже

2021-04-11    347
2021-04-09    283
2021-04-10    255
2021-04-08    240
Name: date, dtype: int64


df_ad_sys_svod[:1]# сравним суммы по выручкам в двух таблицах


mistake = df_billing_sys[(df_billing_sys['affid'] == 1008) & (df_billing_sys['date'] == '2021-04-09')]['amount'].sum() -\
df_ad_sys_svod.iloc[0,0]
print('По выручке в таблицах df_billing_sys и df_ad_sys на каждую дату есть различия (разница на 2021-04-09): \n', round(mistake,2))
# По выручке в таблицах df_billing_sys и df_ad_sys на каждую дату есть различия (разница на 2021-04-08):
# тут надо подумать с чем это может быть связано, всё зависит от логики работы систем, мне кажется, что
# в одной системе транзакция смотрится по переходу на страницу перевода денег, в другой смотрится именно поступление денег,
# НО это лишь догадка

По выручке в таблицах df_billing_sys и df_ad_sys на каждую дату есть различия (разница на 2021-04-09): 
 95.01


# здесь также используем наши параметры даты для выборки из таблицы по дате
df_billing_sys['date'] = pd.to_datetime(df_billing_sys['date'],  format='%Y-%m-%d %H:%M:%S')
df_billing_sys = df_billing_sys[(df_billing_sys['date'] >= date_parametr[0]) & (df_billing_sys['date'] <= date_parametr[1])]
df_billing_sys.value_counts('date')

date
2021-04-11    347
2021-04-09    283
2021-04-10    255
dtype: int64


df_billing_sys[:1]


df_billing_sys_svod = df_billing_sys.pivot_table(values = 'amount',
                           columns = 'date',
                           index = 'affid',
                           aggfunc = 'sum'
                          )
df_billing_sys_svod.fillna(0, inplace=True) 
# я заменяю NaN на 0, подозревая, что если NaN, то данные о выручке не поступали, значит её нет
df_billing_sys_svod


df_billing_sys_svod.sum(axis=1).index.astype(int).astype(str)

Index(['1000', '1008', '1009', '1012', '1026', '1042', '1209', '1210', '1211',
       '1238', '1401', '1402', '1510', '1997', '1998', '1999', '9999'],
      dtype='object', name='affid')


df_billing_sys_svod


revenue_billing_sys = df_billing_sys_svod.sum().sum()
import plotly.graph_objects as go
fig = go.Figure(go.Indicator(
    title = {'text': "Выручка по данным Cистемы биллинга с {begin} по {end}".format(begin=str(date_parametr[0])[:10], 
                                                                                     end=str(date_parametr[1])[:10])},
     mode = "number+delta",
    value = revenue_billing_sys,
    delta = {'reference': revenue_billing_sys}))
fig.show()


df_billing_sys_svod.sum(axis=1).sort_values().to_list()#.iloc[0]
df_billing_sys_svod.sum(axis=1).sort_values().index.to_list()

cufflinks.go_offline()
trace0 = go.Bar(
    x=df_billing_sys_svod.sum(axis=1).sort_values(ascending=False).index.astype(int).astype(str),
    y=df_billing_sys_svod.sum(axis=1).sort_values(ascending=False).to_list(),
    name='revenue df_billing_sys'
)

data = [trace0]
layout = {'title': 'Revenue из Рекламной системы в разрезе источников трафика', 'xaxis': {'title': 'Date'}, 'yaxis': {'title': 'Revenue'}}
fig = go.Figure(data=data, layout=layout)
iplot(fig, show_link=False)


cols = df_billing_sys_svod.columns
cols = pd.to_datetime(cols,  format='%Y-%m-%d %H:%M:%S')


import plotly.graph_objects as go
from plotly.offline import iplot
import cufflinks

cufflinks.go_offline()

traces = [go.Bar(
    x=cols,
    y=df_billing_sys_svod.iloc[i].values,
    name='{}'.format(df_billing_sys_svod.index[i])) for i in range(len(df_billing_sys_svod))]

data = traces
layout = {'title': 'Revenue из Биллинг системы в разрезе дат и источников трафика', 'xaxis': {'title': 'Date'}, 'yaxis': {'title': 'Revenue'}}
fig = go.Figure(data=data, layout=layout)
iplot(fig, show_link=False)


df_internal_events.info() # опять видим, что есть NaN-ы по источникам трафика, сразу же присвоим им значение 9999

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1459 entries, 0 to 1458
Data columns (total 4 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   created_at  1459 non-null   object 
 1   affid       1343 non-null   float64
 2   revenue     1459 non-null   float64
 3   user_id     1459 non-null   object 
dtypes: float64(2), object(2)
memory usage: 45.7+ KB


df_internal_events.loc[(df_internal_events['affid'].isna()), 'affid'] = 9999
df_internal_events.value_counts('affid')

affid
2010.0    442
1008.0    290
1999.0    164
1210.0    160
9999.0    116
1402.0    110
2999.0     47
1211.0     35
1209.0     27
1009.0     25
1401.0     16
1997.0      6
1998.0      6
1000.0      4
1238.0      3
1042.0      3
1510.0      1
1026.0      1
1012.0      1
2211.0      1
2510.0      1
dtype: int64


df_internal_events['date'] = df_internal_events['created_at'].apply(lambda x: x[:10])
df_internal_events['date'] = pd.to_datetime(df_internal_events['date'],  format='%Y-%m-%d %H:%M:%S')
df_internal_events[:3]


df_internal_events_filtr_date = df_internal_events[(df_internal_events['date'] >= date_parametr[0]) & 
                                                   (df_internal_events['date'] <= date_parametr[1])]


revenue_pixel = df_internal_events_filtr_date['revenue'].sum()
import plotly.graph_objects as go
fig = go.Figure(go.Indicator(
    title = {'text': "Выручка по данным Cистемы биллинга с {begin} по {end}".format(begin=str(date_parametr[0])[:10], 
                                                                                     end=str(date_parametr[1])[:10])},
     mode = "number+delta",
    value = revenue_pixel,
    delta = {'reference': revenue_pixel}))
fig.show()


df_internal_events_filtr_date_svod = df_internal_events_filtr_date.pivot_table(values = 'revenue',
                           columns = 'date',
                           index = 'affid',
                           aggfunc = 'sum'
                          )
df_internal_events_filtr_date.fillna(0, inplace=True) 
# я заменяю NaN на 0, подозревая, что если NaN, то данные о выручке не поступали, значит её нет
df_internal_events_filtr_date_svod


cufflinks.go_offline()
trace0 = go.Bar(
    x=df_internal_events_filtr_date_svod.sum(axis=1).sort_values(ascending=False).index.astype(int).astype(str),
    y=df_internal_events_filtr_date_svod.sum(axis=1).sort_values(ascending=False).to_list(),
    name='revenue df_internal_events'
)

data = [trace0]
layout = {'title': 'Revenue из внутренней системы событий в разрезе источников трафика', 'xaxis': {'title': 'Источник трафика'}, 'yaxis': {'title': 'Revenue'}}
fig = go.Figure(data=data, layout=layout)
iplot(fig, show_link=False)


cols1 = df_internal_events_filtr_date_svod.columns
cols1 = pd.to_datetime(cols1,  format='%Y-%m-%d %H:%M:%S')

cufflinks.go_offline()

traces = [go.Bar(
    x=cols1,
    y=df_internal_events_filtr_date_svod.iloc[i].values,
    name='{}'.format(df_internal_events_filtr_date_svod.index[i])) for i in range(len(df_internal_events_filtr_date_svod))]

data = traces
layout = {'title': 'Revenue из внутренней системы событий в разрезе дат и источников трафика', 'xaxis': {'title': 'Date'}, 'yaxis': {'title': 'Revenue'}}
fig = go.Figure(data=data, layout=layout)
iplot(fig, show_link=False)


df_ad_sys[:2] # я предположил, что spent так же как и revenue имеет здесь накопительный характер


df_ad_sys_svod


spent_traffic_source = df_ad_sys_svod['spent'].sum()

fig = go.Figure(go.Indicator(
    title = {'text': "Затраты на рекламу с {begin} по {end}".format(begin=str(date_parametr[0])[:10], 
                                                                                     end=str(date_parametr[1])[:10])},
     mode = "number+delta",
    value = spent_traffic_source,
    delta = {'reference': spent_traffic_source}))
fig.show()


trace0 = go.Bar(
    x=df_ad_sys_svod.index,
    y=df_ad_sys_svod.revenue,
    name='revenue df_ad_sys'
)

trace1 = go.Bar(
    x=df_ad_sys_svod.index,
    y=df_ad_sys_svod.spent,
    name='spent df_ad_sys'
)

data = [trace0, trace1]
layout = {'title': 'Сравнение Revenue и Spent из Рекламной системы в разрезе дат', 'xaxis': {'title': 'Date'}}
fig = go.Figure(data=data, layout=layout)
iplot(fig, show_link=False)


rashod = df_ad_sys_svod['spent'].sum()
dohod = df_ad_sys_svod['revenue'].sum()

fig = go.Figure()
fig.add_trace(go.Indicator(
    title = {'text': "ROAS в % с {begin} по {end}".format(begin=str(date_parametr[0])[:10], 
                                                                                     end=str(date_parametr[1])[:10])},
    mode = "number",
    value = dohod/rashod*100,
    ))

	created_at	affid	amount	uid
0	2021-04-08 12:27:49 UTC	1008.0	99.0	bf00c161
1	2021-04-11 05:49:51 UTC	1210.0	39.0	c7dc59fc
2	2021-04-09 01:30:36 UTC	1999.0	39.0	da121a2a
3	2021-04-08 03:06:28 UTC	1999.0	39.0	244c8870
4	2021-04-10 20:55:09 UTC	NaN	39.0	f07d2882

	created_at	affid	revenue	user_id
0	2021-04-08 02:46:32.319760 UTC	NaN	39.0	15232625
1	2021-04-08 14:45:35.272310 UTC	1210.0	39.0	15235225
2	2021-04-08 14:59:56.842434 UTC	1009.0	39.0	15235207
3	2021-04-08 16:05:28.244022 UTC	1210.0	39.0	3719420
4	2021-04-08 19:25:10.870616 UTC	1008.0	39.0	15236390

	uid	user_id
0	ab99a80e	15240245
1	f7c8256c	15235689
2	6b7dd846	15233251
3	685c6868	15240351
4	54314e60	15243647

	date	revenue	spent	_collected_at	affid
138	2021-04-08	3766.57	5579.01	2021-04-10 23:42:20.446254 UTC	1008
139	2021-04-09	0.00	55.03	2021-04-09 07:26:50.494610 UTC	1008
140	2021-04-09	0.00	104.60	2021-04-09 07:49:55.634547 UTC	1008
141	2021-04-09	0.00	158.53	2021-04-09 08:13:19.059343 UTC	1008
142	2021-04-09	0.00	219.75	2021-04-09 08:36:02.455092 UTC	1008
143	2021-04-09	0.00	278.28	2021-04-09 08:59:03.086429 UTC	1008
144	2021-04-09	177.00	332.45	2021-04-09 09:22:11.332776 UTC	1008
145	2021-04-09	177.00	398.74	2021-04-09 09:44:44.377286 UTC	1008
146	2021-04-09	177.00	477.70	2021-04-09 10:08:03.600741 UTC	1008
147	2021-04-09	177.00	586.27	2021-04-09 10:31:15.183289 UTC	1008

	created_at	affid	amount	uid
4	2021-04-10 20:55:09 UTC	NaN	39.0	f07d2882
24	2021-04-08 17:27:49 UTC	NaN	39.0	e616fd00
32	2021-04-10 16:58:07 UTC	NaN	39.0	c9e402c9
34	2021-04-10 12:26:32 UTC	NaN	39.0	c38ae8f9
68	2021-04-08 13:40:38 UTC	NaN	39.0	217de917
...	...	...	...	...
1050	2021-04-11 01:44:20 UTC	NaN	17.0	3c5e12cb
1069	2021-04-08 17:07:49 UTC	NaN	17.0	6f1678c5
1074	2021-04-08 04:31:50 UTC	NaN	122.4	3c5e37b7
1080	2021-04-08 01:45:21 UTC	NaN	-39.0	78bbfd33
1082	2021-04-11 01:49:42 UTC	NaN	-39.0	3c5e12cb

Задание¶

Загрузим источники данных и дадим им краткое описание¶

Рекламная система file: ad_sys.csv¶

Биллинг система file: billing_sys.csv¶

Внутренняя система событий по покупкам file: internal_events.csv¶

Для соединения df_internal_events и df_billing_sys¶

Предобработка df_ad_sys¶

Создадим ввод параметра по диапозону дат ¶

revenue_traffic_source- выручка по данным рекламной системы ¶

Предобработка df_billing_sys¶

Сделаем график по таблице df_billing_sys¶

revenue_billing_system- выручка по данным системы биллинга ¶

Revenue из Рекламной системы в разрезе источников трафика¶

Предобработка df_internal_events¶

revenue_pixel- выручка по данным внутренней системы событий ¶

spent_traffic_source- данные по затратам на рекламу ¶

На графике ниже, мы видим что доходы с рекламы меньше, чем расходы на нее. Это не очень хорошо, давайте посчитаем ROAS, чтобы сделать наше предположение более явным¶

	date	spent	_collected_at	affid
0	2021-04-08	10.99	2021-04-08 07:17:50.998301 UTC	1008
1	2021-04-08	46.66	2021-04-08 07:41:11.100207 UTC	1008
2	2021-04-08	78.94	2021-04-08 08:04:40.863896 UTC	1008
3	2021-04-08	113.98	2021-04-08 08:28:42.402099 UTC	1008
4	2021-04-08	147.17	2021-04-08 08:49:53.810297 UTC	1008

	revenue	spent	_collected_at	affid
date
2021-04-09	3552.0	3934.02	2021-04-11 23:51:03.765111 UTC	1008

date	2021-04-09	2021-04-10	2021-04-11
affid
1000.0	0.00	0.00	259.00
1008.0	3647.01	4011.36	5148.95
1009.0	812.00	238.01	393.01
1012.0	39.00	0.00	0.00
1026.0	39.00	0.00	0.00
1042.0	0.00	138.00	39.00
1209.0	398.00	433.40	888.98
1210.0	2087.06	2727.43	2695.98
1211.0	376.01	705.01	763.98
1238.0	0.00	39.00	56.00
1401.0	39.00	255.00	465.99
1402.0	2233.99	1191.00	1581.02
1510.0	0.00	0.00	39.00
1997.0	216.96	0.00	0.00
1998.0	0.00	0.00	127.99
1999.0	1367.82	855.00	1566.88
9999.0	1482.96	2431.99	1401.98

	revenue	spent	_collected_at	affid
date
2021-04-09	3552.00	3934.02	2021-04-11 23:51:03.765111 UTC	1008
2021-04-10	4032.36	6593.73	2021-04-12 23:59:48.838697 UTC	1008
2021-04-11	5269.96	7537.61	2021-04-13 23:46:31.523088 UTC	1008